/**
* @author Max Rupplin
* @date 5-18-08
* 
* A class which serves as a database interface
*/

#include <iostream>
#include "mysql.h"


//BOINC related
#include "sched_msgs.h"
#include <sched_util.h>
#include <backend_lib.h>
#include <boinc_db.h>
#include <parse.h>


//DDAS related
#include "ddas_definitions.h"
#include "test_harness.hpp"
#include "DDAS_DB_Handler.hpp"

using namespace std;

/**
* @author Max Rupplin
* @date 5-18-08
*
* Default constructor
*/
DDAS_DB_Handler::DDAS_DB_Handler(){}

/**
* @author Max Rupplin
* @date 5-18-08
*
* Default destructor
*/
DDAS_DB_Handler::~DDAS_DB_Handler(){}


/**
* Gets the problem IDs of the any new jobs in the DDAS database 'ddas_problem'. The IDs are stored in 'pids'.
*
* @param pids Stores the problem/job IDs from the database call
* @return Returns the number of new jobs found, 0 otherwise
*/
int DDAS_DB_Handler::getNewProblemIDs(int** pids)
{
        //initialize MYSQL
        MYSQL* mysql = mysql_init(0);

        //error detection
        if (!mysql)
        {
                log_messages.printf
                (
                        SCHED_MSG_LOG::MSG_CRITICAL,
                        "DDAS_DB_Handler : getNewProblemIDs : unable to initialize MYSQL.\n"
                );

                //close connection
                mysql_close(mysql);

                return 0;
        }

        //connect to database
        mysql = mysql_real_connect(mysql, DBHOST, USERNAME, PASSWD, DBNAME, 0, 0, 0);

        //error detection
        if (mysql == 0)
        {
                log_messages.printf
                (
                        SCHED_MSG_LOG::MSG_CRITICAL,
                        "DDAS_DB_Handler : getNewProblemIDs : unable to connect to %s@%s\n",
			DBNAME,
			DBHOST
                );

                //close connection
                mysql_close(mysql);

                return 0;
        }

        //perform query + error detection - (status = 0 on new jobs)
        if(mysql_query(mysql, "select problemId from ddas_problem where status=0"))
        {
                log_messages.printf
                (
                        SCHED_MSG_LOG::MSG_CRITICAL,
                        "DDAS_DB_Handler : getNewProblemIDs : The following query failed:\nselect problemId from ddas_problem where status=0\n"
                );

                //close connection
                mysql_close(mysql);

                return 0;
        }
             
	MYSQL_RES*      result = mysql_store_result(mysql);       
	MYSQL_ROW       row;

	//number of new jobs
        unsigned int num_rows  = mysql_num_rows(result);

	//alloc some memory
	*pids = new int[num_rows];
        
	//set value of problemID into pids[]
       	for(unsigned int i=0; i<num_rows; i++)
        {
        	row = mysql_fetch_row(result);
        	(*pids)[i] = atoi(row[0]);
        }

        //free memory
        mysql_free_result(result);

        //close connection
        mysql_close(mysql);

	//return num_rows
        return num_rows;
}

/**
* Returns the quorum size for a given job
*
* @param job_id The job whose quorum size in which we are interested
* @ return Returns 1 if successful, 0 otherwise
*/
int DDAS_DB_Handler::getQuorumSize( int job_id )
{
        char buffer[1024];

        MYSQL* mysql = mysql_init(0);

        //error handling
        if (!mysql)
        {
                log_messages.printf
                (
                        SCHED_MSG_LOG::MSG_CRITICAL,
                        "DDAS_Work_Handler : getQuorumSize : unable to initialize MYSQL.\n"
                );

                //close connection
                mysql_close(mysql);

                return 0;
        }

        //actually connect to DB
        mysql = mysql_real_connect(mysql, DBHOST, USERNAME, PASSWD, DBNAME, 0, 0, 0);

        //error handling
        if (mysql == 0)
        {
                log_messages.printf
                (
                        SCHED_MSG_LOG::MSG_CRITICAL,
                        "DDAS_Work_Handler : getQuorumSize : unable to connect to %s@%s.\n",
                        DBNAME,
                        DBHOST
                );

                //close connection
                mysql_close(mysql);

		//error
                return 0;
        }

	sprintf( buffer, "select quorumSize from ddas_problem where problemId = %d", job_id);

        //execute query + error handling
        if( mysql_query( mysql, buffer ) )
        {
                log_messages.printf
                (
                        SCHED_MSG_LOG::MSG_CRITICAL,
                        "DDAS_Work_Handler : getQuorumSize : The following query failed.\n%s\n",
			buffer
                );

                //close connection
                mysql_close(mysql);

		//error
                return 0;
        }

        MYSQL_RES*      result  = mysql_store_result(mysql);
        MYSQL_ROW       row     = mysql_fetch_row(result);

	int retval = atoi(row[0]);

	mysql_free_result(result);
	mysql_close(mysql);

	return retval;
}

/**
* Returns the number of unsent results for a given job
*
* @param job_id The job in question
* @return Returns 1 if successful, 0 otherwise
*/
int DDAS_DB_Handler::getWorkCushion( int job_id )
{
        char buffer[1024];

	MYSQL* mysql = mysql_init(0);

        //error handling
        if (!mysql)
        {
                log_messages.printf
                (
                        SCHED_MSG_LOG::MSG_CRITICAL,
                        "DDAS_Work_Handler : getWorkunitCushion : unable to initialize MYSQL.\n"
                );

                //close connection
                mysql_close(mysql);

                return 0;
        }

        //actually connect to DB
        mysql = mysql_real_connect(mysql, DBHOST, USERNAME, PASSWD, DBNAME, 0, 0, 0);

        //error handling
        if (mysql == 0)
        {
                log_messages.printf
                (
                        SCHED_MSG_LOG::MSG_CRITICAL,
                        "DDAS_Work_Handler : getWorkunitCushion : unable to connect to %s@%s.\n",
                        DBNAME,
                        DBHOST
                );

                //close connection
                mysql_close(mysql);

                return 0;
        }
	
	//create SQL statement
	sprintf( buffer, "select count(*) from workunit join workunit_to_job_mapping on workunit.id = workunit_to_job_mapping.wu_id where workunit.canonical_resultid =0 and workunit_to_job_mapping.job_id = %d", job_id);
//	sprintf( buffer, "select count(*) from result join workunit on result.workunitid = workunit.id join workunit_to_job_mapping on workunit_to_job_mapping.wu_id = workunit.id where sent_time = 0 and workunit_to_job_mapping.job_id = %u", job_id );

        //execute query + error handling
        if( mysql_query( mysql, buffer ) )
        {
                log_messages.printf
                (
                        SCHED_MSG_LOG::MSG_CRITICAL,
                        "DDAS_Work_Handler : getWorkunitCushion : The following query failed.\n%s\n",
			buffer
                );

                //close connection
                mysql_close(mysql);

                return 0;
        }


        MYSQL_RES*      result  = mysql_store_result(mysql);
        MYSQL_ROW       row     = mysql_fetch_row(result);


	//store result
	int retval = atoi(row[0]);

        //free memory
        mysql_free_result(result);

        //close connection
        mysql_close(mysql);

	return retval;	
}

/**
* After a job has had all of its work processed, this function should be called to tell the database that the job requires no more attention for the work generator
*
* @param job_id The job in question
* @return Returns 1 if successful, 0 otherwise
*/
int DDAS_DB_Handler::setAllWorkCompleteForJob( int job_id )
{
        #ifdef DEBUG_MODE
        cout<<"set_job_to_processed was called."<<endl;
        #endif

        //space for the MYSQL prepared statement
        char* buffer = new char[512];

        //initialization call
        MYSQL* mysql = mysql_init(0);

        //error handling
        if (!mysql)
        {
                log_messages.printf
                (
                        SCHED_MSG_LOG::MSG_CRITICAL,
                        "DDAS_DB_Handler : setJobToProcessed : unable to initialize MYSQL.\n"
                );

                #ifdef DEBUG_MODE
                cout<<"MYSQL initialization failed..."<<endl;
                #endif

                //close connection
                mysql_close(mysql);

                return 0;
        }

        //actually connect to DB
        mysql = mysql_real_connect(mysql, DBHOST, USERNAME, PASSWD, DBNAME, 0, 0, 0);

        //error handling
        if (mysql == 0)
        {
                log_messages.printf
                (
                        SCHED_MSG_LOG::MSG_CRITICAL,
                        "DDAS_DB_Handler : setJobToProcessed : unable to connect to %s@%s.\n",
                        DBNAME,
                        DBHOST
                );

                #ifdef DEBUG_MODE
                cout<<"MYSQL connection failed..."<<endl;
                #endif

                //close connection
                mysql_close(mysql);

                return 0;
        }
        else
        {
                #ifdef DEBUG_MODE
                cout<<"MYSQL connection established..."<<endl;
                #endif
        }

        //a prepared statement
        sprintf(buffer, "update ddas_problem set status = 1 where problemId = %u;", job_id);

        //execute query + error handling
        if( mysql_query( mysql, buffer ) )
        {
                log_messages.printf
                (
                        SCHED_MSG_LOG::MSG_CRITICAL,
                        "DDAS_DB_Handler : setJobToProcessed: mysql_query failed.\n"
                );

                #ifdef DEBUG_MODE
                cout<<"MYSQL update failed..."<<endl;
                #endif

                //close connection
                mysql_close(mysql);

                return 0;
        }
        else
        {
                #ifdef DEBUG_MODE
                cout<<"MySQL update succeeded ..."<<endl;
                #endif

                //close connection
                mysql_close(mysql);

                //free memory
                delete[] buffer;

                return 1;
        }
}

/**
* Initializes variables using a call to the DDAS database.  The table referenced is 'ddas_problem'.
*
* @param pid The problem ID - the unique identifier for a job
* @param uid The user ID - the unique identifier for a researcher
* @param numX The number of independent variables
* @param numY The number of dependent variables
* @param numRows The number of rows in the data set defined by 'pid'
* @param numResults The number of results the researcher expects
* @param quorumSize The quorum size specified by the researcher during project submission
* @return Returns 1 if successful, 0 otherwise.
*/
int DDAS_DB_Handler::initVarsFrom_ddas_problem( int pid, long* uid, long* numX, long* numY, long* numRows, long* numResults, long* quorumSize)
{
        #ifdef DEBUG_MODE
        cout<<"init_from_database was called."<<endl;
        #endif

        //space for prepared statement
        char* buffer = new char[256];

        //initialize MYSQL
        MYSQL* mysql = mysql_init(0);

        //error detection
        if (!mysql)
        {
                log_messages.printf
                (
                        SCHED_MSG_LOG::MSG_CRITICAL,
                        "DDAS_Work_Handler : init_from_database : unable to initialize MYSQL.\n"
                );

                #ifdef DEBUG_MODE
                cout<<"MYSQL initialization failed..."<<endl;
                #endif

                //close connection
                mysql_close(mysql);

                //free memory
                delete[] buffer;

                return 0;
        }

        //connect to DB
        mysql = mysql_real_connect(mysql, DBHOST, USERNAME, PASSWD, DBNAME, 0, 0, 0);

        //error detection
        if (mysql == 0)
        {
                log_messages.printf
                (
                        SCHED_MSG_LOG::MSG_CRITICAL,
                        "DDAS_Work_Handler : init_from_database : unable to connect to database.\n"
                );

                #ifdef DEBUG_MODE
                cout<<"MYSQL connection failed..."<<endl;
                #endif

                //close connection
                mysql_close(mysql);

                //free memory
                delete[] buffer;

                return 0;
        }
        else
        {
                #ifdef DEBUG_MODE
                cout<<"MYSQL connection established..."<<endl;
                #endif
        }

        //prepared statement
        sprintf(buffer, "select * from ddas_problem where problemId = %u", pid);

        //perform query + error detection
        if(mysql_query(mysql, buffer))
        {
                log_messages.printf
                (
                        SCHED_MSG_LOG::MSG_CRITICAL,
                        "DDAS_Work_Handler : init_from_database : mysql_query failed.\n"
                );

                #ifdef DEBUG_MODE
                cout<<"MYSQL query failed..."<<endl;
                #endif

                //close connection
                mysql_close(mysql);

                //free memory
                delete[] buffer;

                return 0;
        }
        else
        {
                MYSQL_RES*      result  = mysql_store_result(mysql);
                MYSQL_ROW       row     = mysql_fetch_row(result);

                #ifdef DEBUG_MODE
                cout<<row[1]<<endl;
                cout<<row[2]<<endl;
                cout<<row[3]<<endl;
                cout<<row[4]<<endl;
                cout<<row[5]<<endl;
                cout<<row[7]<<endl;
                #endif

                *uid            = atol(row[1]);
                *numResults     = atol(row[2]);
                *numRows        = atol(row[3]);
                *numX           = atol(row[4]);
                *numY           = atol(row[5]);
                *quorumSize     = atol(row[7]);

                //free memory
                mysql_free_result(result);

                //close connection
                mysql_close(mysql);

                #ifdef DEBUG_MODE
                cout<<"MySQL query returning from init_from_database()."<<endl;
                #endif

                //free memory
                delete[] buffer;

                return 1;
        }
}


/**
* This function serves to initialize variables for the work generator daemon
*
* @param job_id The job in question
* @param uid The user id of the person who submitted the job
* @param numX The number of independent variables in the data set
* @param numY The number of dependent variables in the data set
* @param numRows The number of rows in the data set
* @param numResults The number of results expected by the researcher
* @param quorumSize The quorum size specified by the researcher
* @param workunit_count The number of workunits this job will require, in total
* @param workunit_stride The number of regressions a single work unit will be responsible for
* @param total_combinations The total number of combinations possible for this job (16 columns = 65535 combinations)
* @return Returns 1 if successful, 0 otherwise
*/

int DDAS_DB_Handler::initWorkgenVars( int job_id, long* uid, long* numX, long* numY, long* numRows, long* numResults, long* quorumSize, verylong* workunit_count, verylong* workunit_stride, verylong* total_combinations )
{
	//init subset of parameters
	this->initVarsFrom_ddas_problem( job_id, uid, numX, numY, numRows, numResults, quorumSize );

	verylong two = 0;
	verylong numX_vl = 0;
	verylong numRows_vl = 0;
	verylong min_wu_dimension = 0;
	verylong job_dimension = 0;
	verylong remainder = 0;	

        #ifdef DEBUG_MODE
        cout<<"pid: "<<pid<<endl;
        cout<<"uid: "<<uid<<endl;
        cout<<"numX: "<<numX<<endl;
        cout<<"numY: "<<numY<<endl;
        cout<<"numRows: "<<numRows<<endl;
        cout<<"numResults: "<<numResults<<endl;
        cout<<"quorumSize: "<<quorumSize<<endl;
        #endif

        //BOINC helper objects
        DB_APP app;
        DB_WORKUNIT wu;
        SCHED_CONFIG config;

        //convert from long to verylong
        zintoz( 2l, &two );
        zintoz( *numX, &numX_vl );
        zintoz( *numRows, &numRows_vl);

        #ifdef DEBUG_MODE
        cout<<"WORKUNIT_DIMENSION: "<<WORKUNIT_DIMENSION<<endl;
        #endif

        zintoz( WORKUNIT_DIMENSION, &min_wu_dimension );

        int retval;

        char wu_template[LARGE_BLOB_SIZE];

        //initialize config using config.xml
        retval = config.parse_file("..");
        if (retval)
        {
                log_messages.printf
                (
                        SCHED_MSG_LOG::MSG_CRITICAL,
                        "DDAS_Work_Handler : make_work : unable to parse config.xml.\n"
                );

                return 0;
        }

        //BOINC's utility function to connect to database
        retval = boinc_db.open(config.db_name, config.db_host, config.db_user, config.db_passwd);
        if (retval)
        {
                log_messages.printf
                (
                        SCHED_MSG_LOG::MSG_CRITICAL,
                        "DDAS_Work_Handler : make_work : unable to connect to database.\n"
                );

                return 0;
        }

        //initialize DB_APP from database call
        retval = app.lookup("where name='ddas'");
        if (retval)
        {
                log_messages.printf
                (
                        SCHED_MSG_LOG::MSG_CRITICAL,
                        "DDAS_Work_Handler : make_work : failed to initialize DB_APP.\n"
                );

                return 0;
        }

        //read workunit template into 'wu_template'
        retval = read_filename("../templates/ddas_wu", wu_template, sizeof(wu_template));
        if (retval)
        {
                log_messages.printf
                (
                        SCHED_MSG_LOG::MSG_CRITICAL,
                        "DDAS_Work_Handler : make_work : unable to read workunit template.\n"
                );

                return 0;
        }


        //how many combinations are required for all possible regressions: 2^numX ?
        zexp( two, numX_vl, total_combinations );

        //calculate the job's problem space: (all combinations x numRows)
        zmul( *total_combinations, numRows_vl, &job_dimension );

        //calculate how many workunits will be needed
        zdiv( job_dimension, min_wu_dimension, workunit_count, &remainder);

        //if workunit_count has a remainder add one to its value to account for the tail end of the job
        if( !ziszero(remainder) ) zsadd( *workunit_count, 1l, workunit_count);

        //how many workunits of size (workunit_stride x numRows) does the job require in total?
        zdiv( *total_combinations, *workunit_count, workunit_stride, &remainder);

        #ifdef DEBUG_MODE
        cout<<"jobID:"<<jobID<<endl;
        cout<<"total_combinations:";    zwriteln(total_combinations);
        cout<<"workunit_stride:";       zwriteln(workunit_stride);
        cout<<"workunit_count:";        zwriteln(workunit_count);
        cout<<"job_dimension:";         zwriteln(job_dimension);
        #endif
}


/**
* Checks if all work has been created for a given job. If current_iteration >= final_iteration then it returns true, false otherwise
*
* @param job_id The job in question
* @param current_iteration The iteration the work generator is currently on
* @return Returns 1 if successful, 0 otherwise
*/
int DDAS_DB_Handler::hasAllWorkBeenCreatedForJob( int job_id, long current_iteration )
{
	long final_iteration = getTotalWorkunitsRequiredForJob( job_id );

	if( current_iteration >= final_iteration ) return 1;

	return 0;
}


/**
* Return the current workgen iteration for a job.  If a job has 10,000 work units, and 0-4999 have been created, the current should be 5000.
*
* @param job_id The job in question
* @return Returns 1 if successful, 0 otherwise
*/
unsigned long DDAS_DB_Handler::getCurrentWorkgenIteration( int job_id )
{
	char buffer[1024];

	MYSQL* mysql = mysql_init(0);

	mysql = mysql_real_connect(mysql, DBHOST, USERNAME, PASSWD, DDAS, 0, 0, 0);

        if( mysql==0 )
        {
		
                return 0;
        }

        sprintf( buffer, "select current_workgen_iteration from ddas_problem where problemId = %u", job_id );

        //execute update + check for error
        if( mysql_query(mysql, buffer) )
        {
                //close connection
                mysql_close(mysql);

                //log error
		cout<<"DDAS_DB_Handler : getCurrentWorkgenIteration : The following query failed:"<<endl<<buffer<<endl;

                //return error
                return 0;
        }

        MYSQL_RES*      result  = mysql_store_result(mysql);
       	MYSQL_ROW       row     = mysql_fetch_row(result);

	//store value
        long retval = atol(row[0]);

	//free memory
	mysql_free_result(result);

        //close connection
        mysql_close(mysql);

        //return success
        return retval;
}


/**
* Updates current_workgen_iteration in ddas_problem, for a particular job
*
* @param job_id The job in question
* @param current_iteration The current iteration from the work generator
* @return Returns 1 if successful, 0 otherwise
*/
int DDAS_DB_Handler::setCurrentWorkgenIteration( int job_id, long current_iteration )
{
        char buffer[1024];

        MYSQL* mysql = mysql_init(0);

        mysql = mysql_real_connect(mysql, DBHOST, USERNAME, PASSWD, DDAS, 0, 0, 0);

        if( mysql==0 ) return 0;

	//perform update 
        sprintf( buffer, "update ddas_problem set current_workgen_iteration = %u where problemId = %u", current_iteration, job_id );

        //execute update + check for error
        if( mysql_query(mysql, buffer) )
        {
                //close connection
                mysql_close(mysql);

                //log error

                //return error
                return 0;
        }

	//close connection        
	mysql_close(mysql);
        
	//return success
        return 1;
}


/**
* Finds the last work unit to be created for a job given its starting_value, its final iteration and DESIRED_WU_CUSHION.  The purpose is to determine how many work units should be created for a given iteration of the work generator daemon.
* 
* @param job_id The job in question
* @param starting_value The current iteration of work unit
* @return The final iteration for a cycle of the work generator daemon for this particular job
*/
unsigned long DDAS_DB_Handler::getFinalWorkUnitIterationForCurrentWorkgenCycle( int job_id, long starting_value )
{
	int new_work_units_required; 
	long final_work_unit_iteration;

	//how many work units should we create?
	new_work_units_required = DESIRED_WU_CUSHION - this->getWorkCushion( job_id );

	//how many work units, in total, does this job require?
	final_work_unit_iteration = this->getTotalWorkunitsRequiredForJob( job_id );

	//if no new units are required just return starting_value
	if( new_work_units_required <=0 ) return (unsigned long)starting_value;

	//if starting_value+required_padding puts us past the final workgen iteration for this job just return final_iteration
	if( (starting_value+new_work_units_required) >= final_work_unit_iteration ) return final_work_unit_iteration;

	//if not past the final_work_unit_iteration then return that value
	return (starting_value + new_work_units_required);	
}

/**
* Returns the total number of work units which must be created for a given job, in total. This is a database call whereas calculateTotalWorkunitsForJob is a dynamic calculation. This should be used if the data has already been inserted into the database.
* 
* @param job_id The job in question
* @return The number of work units which must be created, in total, for a given job
*/
unsigned long DDAS_DB_Handler::getTotalWorkunitsRequiredForJob( int job_id )
{
        char buffer[1024];

        MYSQL* mysql = mysql_init(0);

        mysql = mysql_real_connect(mysql, DBHOST, USERNAME, PASSWD, DDAS, 0, 0, 0);

        if( mysql==0 )
        {
                return 0;
        }

        sprintf( buffer, "select final_workgen_iteration from ddas_problem where problemId = %u", job_id );

        //execute update + check for error
        if( mysql_query(mysql, buffer) )
        {
                //close connection
                mysql_close(mysql);

                //log error
                cout<<"DDAS_DB_Handler : setFinalIterationForJob : The following query failed:"<<endl<<buffer<<endl;

                //return error
                return 0;
        }

        MYSQL_RES* result = mysql_store_result(mysql);

        MYSQL_ROW row = mysql_fetch_row(result);

        int retval = atoi(row[0]);

        mysql_free_result(result);

        mysql_close(mysql);

        return retval;
}


/**
* Finds the last workunit iteration for a given job through calculation, as opposed to getTotalWorkunitsForJob, which is database driven.  This should be used if the database does not already have this result.
*
* @param job_id The job in question
* @return The number of work unhits which must be created, in total, for a given job
*/
unsigned long DDAS_DB_Handler::calculateTotalWorkunitsRequiredForJob( int job_id )
{
        //variables to be set by init_from_database
        long numX=0;
        long numY=0;
        long numRows=0;
        long numResults=0;
        long problemId=0;
        long jobID=0;
        long uid=0;
        long quorumSize=0;

        verylong workunit_count=0;
        verylong workunit_stride=0;
        verylong total_combinations=0;

        //initialize required vars
        this->initWorkgenVars( job_id, &uid, &numX, &numY, &numRows, &numResults, &quorumSize, &workunit_count, &workunit_stride, &total_combinations );

	return ztoint(workunit_count);
}

/**
* Sets the last workunit iteration for a given job into the database
*
* @param job_id The job in question
* @param iteration The final iteration to be stored
* @return Returns 1 if successful, 0 otherwise
*/
int DDAS_DB_Handler::setTotalWorkunitsRequiredForJob( int job_id, unsigned long iteration )
{
        char buffer[1024];

        MYSQL* mysql = mysql_init(0);

        mysql = mysql_real_connect(mysql, DBHOST, USERNAME, PASSWD, DDAS, 0, 0, 0);

        if( mysql==0 )
        {
                return 0;
        }

        sprintf( buffer, "update ddas_problem set final_workgen_iteration = %u where problemId = %u", iteration, job_id );

        //execute update + check for error
        if( mysql_query(mysql, buffer) )
        {
                //close connection
                mysql_close(mysql);

                //log error
		cout<<"DDAS_DB_Handler : setFinalIterationForJob : The following query failed:"<<endl<<buffer<<endl;

                //return error
                return 0;
        }

	mysql_close(mysql);

	return 1;
}


/**
* @author Max Rupplin
* @date 5-18-08 
* @param job_id The job in question
* @return 1 if successful, 0 otherwise
*
* 'markJobAsComplete' marks a job as complete in the ddas database by setting the completion_timestamp field to NOW()
*/
int DDAS_DB_Handler::markJobAsComplete( int job_id )
{
        MYSQL* mysql = mysql_init(0);

        mysql = mysql_real_connect(mysql, DBHOST, USERNAME, PASSWD, DDAS, 0, 0, 0);

        if( mysql==0 )
        {
                return 0;
        }

        char buffer[1024];

        sprintf( buffer, "update ddas_problem set completion_timestamp = now() where problemId = %u", job_id );

	//execute update + check for error
        if( mysql_query(mysql, buffer) )
        {
		//close connection
                mysql_close(mysql);

		//log error

		//return error
                return 0;
        }	

	//close connection
	mysql_close(mysql);
	
	//return success
	return 1;
}

/**
* @author Max Rupplin
* @date 5-18-08
* @param size A pointer to the number of results (the size of results) returned by the database query
* @param completed_jobs A pointer to an array of ints used to store the Job IDs
* @return 1 if successful, 0 otherwise
*
* 'getUnprocessedCompleteJobs' returns any completed jobs, that have not been previously considered, as an int[] containing zero or more job IDs
*/
int DDAS_DB_Handler::getUnprocessedCompleteJobs( int* size, int** completed_jobs )
{
        MYSQL* mysql = mysql_init(0);

        mysql = mysql_real_connect(mysql, DBHOST, USERNAME, PASSWD, DDAS, 0, 0, 0);

        if( mysql==0 )
        {
                return 0;
        }

        char* buffer = "select problemId from ddas_problem where year(completion_timestamp) = 0";

        //execute update + check for error
        if( mysql_query(mysql, buffer) )
        {
		cout<<"DDAS_DB_Handler : getUnprocessedCompleteJobs : The following query failed:"<<endl<<buffer<<endl;

                //close connection
                mysql_close(mysql);

                //log error

                //return error
                return 0;
        }	

	//store result
	MYSQL_RES* result = mysql_store_result(mysql);
	
	//a row
	//MYSQL_ROW row;

	//set size
	*size = mysql_num_rows(result);

	//if no newly completed jobs
	if( *size==0 )
	{
		//log result
                log_messages.printf
                (
                        SCHED_MSG_LOG::MSG_NORMAL,
                        "DDAS_DB_Handler::getUnprocessedCompleteJobs found no new, unprocessed jobs.\n"
                );

		//return success
		return 1;
	}

	//alloc some memory for array
	*completed_jobs = new int[*size];

	//for each row in result
	for( int i=0; i<*size; i++ )
	{
		//get next row
		MYSQL_ROW row = mysql_fetch_row(result);

		//get and store problemId
		(*completed_jobs)[i] = atoi(row[0]); 
	}

	//free result
	mysql_free_result(result);

	//close connection
	mysql_close(mysql);

	//return success
	return 1;
}


/**
* @author Max Rupplin
* @date 5-18-08
* @param job_id The job in question
* @return The number of results to be returned (top N) specified by the researcher for a given job, 0 if not found or error occurred
*
* 'getNumResults' returns the # of results (numResults) specified by the researcher for a given job 
*/
int DDAS_DB_Handler::getNumResults( int job_id )
{
	MYSQL* mysql = mysql_init(0);

	mysql = mysql_real_connect(mysql, DBHOST, USERNAME, PASSWD, DDAS, 0, 0, 0);

	if( mysql==0 )
	{
		return 0;
	}

	char buffer[1024];

	sprintf( buffer, "select numResults from ddas_problem where problemId = %u", job_id );
	
	if( mysql_query(mysql, buffer) )
	{
		mysql_close(mysql);

		return 0;
	}
	
	MYSQL_RES* result = mysql_store_result(mysql);

	MYSQL_ROW row = mysql_fetch_row(result);

	mysql_free_result(result);

	mysql_close(mysql);

	//return the value 
	return atoi(row[0]);
}


/**
* @author Max Rupplin
* @date 5-18-08
* @param result_id The result from which the independent variable count can be derived
* @return The number of independent variables in the data set associated with 'job_id', 0 if not found or error occurred
*
* 'getIndependentVarCountFromResult' retrieves numColX (# of independent variables) from the ddas database for a given result 
*/
int DDAS_DB_Handler::getIndependentVarCountFromResult( int result_id )
{
        char buffer[1024];

	MYSQL* mysql = mysql_init(0);

        mysql = mysql_real_connect(mysql, DBHOST, USERNAME, PASSWD, DDAS, 0, 0, 0);

        if( mysql==0 )
        {
                cout<<"DDAS_DB_Handler : getIndependentVarCount: Unable to connect to "<<DDAS<<"@"<<DBHOST<<endl;

                return 0;
        }

	sprintf( buffer, "select numColX from ddas_problem join workunit_to_job_mapping join workunit join result on problemId = job_id AND workunit_to_job_mapping.wu_id = workunit.id AND workunit.id = result.workunitid where result.id = %d", result_id);

        if( mysql_query(mysql, buffer) )
        {
                mysql_close(mysql);

		cout<<"Empty set or failed query..."<<endl;

                return 0;
        }

	MYSQL_RES* result = mysql_store_result(mysql);

	MYSQL_ROW row = mysql_fetch_row(result);
        
	mysql_close(mysql);

       	mysql_free_result(result);


	if( mysql_num_rows(result) ==0 )
	{
		cout<<"Empty set..."<<endl;
		return 0;
	}

	return atoi(row[0]);
}

/**
* @author Max Rupplin
* @date 5-18-08
* @param job_id The job in question
* @return The number of independent variables in the data set associated with 'job_id', 0 if not found or error occurred
*
* 'getIndependentVarCountFromJob' retrieves numColX (# of independent variables) from the ddas database for a given job
*/
int DDAS_DB_Handler::getIndependentVarCountFromJob( int job_id )
{
        MYSQL* mysql = mysql_init(0);

        mysql = mysql_real_connect(mysql, DBHOST, USERNAME, PASSWD, DDAS, 0, 0, 0);

        if( mysql==0 )
        {
		cout<<"DDAS_DB_Handler : getIndependentVarCount: Unable to connect to "<<DDAS<<"@"<<DBHOST<<endl;

                return 0;
        }

	char buffer[1024]; 

        sprintf( buffer, "select numColX from ddas_problem where problemId = %u", job_id );

        if( mysql_query(mysql, buffer) )
        {
		cout<<"DDAS_DB_Handler : getIndependentVarCount: The following query failed:"<<endl<<buffer<<endl;

                mysql_close(mysql);

                return 0;
        }

        MYSQL_RES* result = mysql_store_result(mysql);

	//if no results returned
	if( mysql_num_rows(result) == 0 ) 
	{
		cout<<"DDAS_DB_Handler : getIndependentVarCount: No rows returned by the following query:"<<endl<<buffer<<endl;

		mysql_free_result(result);

		return 0;
	}

	MYSQL_ROW row = mysql_fetch_row(result);

	int retval = atoi(row[0]);

        mysql_free_result(result);

        mysql_close(mysql);

	//return result
        return retval;
}

/**
* @author Max Rupplin
* @date 5-18-08
* @param job_id The job in question
* @param num_results How many results constitute the top N results
* @param results A struct to hold the data from the top N results
* @return 1 if successful, 0 otherwise
*
* 'getTopNResults' gets the top N results and stores them in a DDAS_CLIENT_RESULT_DATA struct
*/
int DDAS_DB_Handler::getTopNResults( int job_id, int num_results, DDAS_Client_Result_Data* results )
{
        MYSQL* mysql = mysql_init(0);

        mysql = mysql_real_connect(mysql, DBHOST, USERNAME, PASSWD, SCIENCE, 0, 0, 0);

        if( mysql==0 )
        {
                return 0;
        }

        char buffer[2048]; 

	int num_coeffs = 1+(this->getIndependentVarCountFromJob( job_id ));

	//grab the definition numbers representing the top N results for a given job
	sprintf( buffer, "select data_rows.definition_number, data_rows.binary_representation, data_rows.sse, regression_coefficients.column_index, regression_coefficients.value from data_rows join regression_coefficients join canonical_result_mappings on canonical_result_mappings.canonical_result_id=data_rows.canonical_result_id AND data_rows.canonical_result_id = regression_coefficients.canonical_result_id AND data_rows.definition_number = regression_coefficients.definition_number and job_id = %u order by sse, data_rows.definition_number asc limit %u", job_id, num_results*num_coeffs );

	//execute query + check for error
        if( mysql_query(mysql, buffer) )
        {
                log_messages.printf
                (
                        SCHED_MSG_LOG::MSG_CRITICAL,
                        "DDAS_DB_Handler::getTopNResults failed on the following query:\n%s\n",
			buffer
                );

		//close connection
                mysql_close(mysql);

		//return error
                return 0;
        }

	//store result 
	MYSQL_RES* result = mysql_store_result(mysql);

	//a simple but important check to see that the number which should jive, in fact do
	if( num_results*num_coeffs != mysql_num_rows(result) )
	{
		//oops handle this
                log_messages.printf
                (
                        SCHED_MSG_LOG::MSG_DEBUG,
                        "DDAS_DB_Handler::getTopNResults detected a logical error:\nThe number of rows returned by getTopNResults is incorrect. It should return %s but in fact returns %s.\n",
			(num_results*num_coeffs),
			mysql_num_rows(result)
                );

		//return error
		return 0;
	}

	if(mysql_num_rows(result) <= 0)
	{
                //oops handle this
                log_messages.printf
                (
                        SCHED_MSG_LOG::MSG_DEBUG,
                        "DDAS_DB_Handler : getTopNResults : The following query returned an empty set:\n%s\n",
                        buffer
                );

                //return error
                return 0;
	}

	//now build the DDAS_Client_Result_Data object
	Coefficients** coeff_ptr;
	
	//grab the pointer to the coefficients
	results->getCoefficients(&coeff_ptr);


	//for each result
	int result_index=-1;
	for( int i=0; i<mysql_num_rows(result); i++)
	{
		MYSQL_ROW row = mysql_fetch_row(result);
	
		if( i%num_coeffs==0 )
		{	
			result_index++;

                	coeff_ptr[result_index]->setCoefficient(0, atof(row[4]));

			results->setDefinitionNumber(row[0], result_index);
			results->setBinaryRepresentation(row[1], result_index);
			results->setSSE(atof(row[2]), result_index);
		}
		else
		{
			coeff_ptr[result_index]->setCoefficient(i%num_coeffs, atof(row[4]));			
		}
	}

	//free memory
	mysql_free_result(result);

	//close connection
	mysql_close(mysql);

	//return success
	return 1;
}
